package sistkaraoke.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import sistkaraoke.excepcion.DAOExcepcion;
import sistkaraoke.modelo.*;
import sistkaraoke.util.ConexionBD;

public class LocalDAO extends BaseDAO implements ILocalDAO{
	
	public int insertar(ILocal objLocal) throws DAOExcepcion {
		String query = "Exec KARAOKE.USP_ADD_LOCAL ?,?,?,?,?,?;";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		int i = 0;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, objLocal.getNombre());
			stmt.setString(2, objLocal.getDireccion());
			stmt.setString(3, objLocal.getTelefono());
			stmt.setString(4, objLocal.getCorreo());
			stmt.setString(5, objLocal.getDescripcion());
			stmt.setInt(6, objLocal.getAdministrador().getId());		
			
			i = stmt.executeUpdate();
			
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}

		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		
		return i;
	}
	
	public int actualizar(ILocal objLocal) throws DAOExcepcion {
		String query = "Exec KARAOKE.USP_UPDATE_LOCAL ?,?,?,?,?,?;";
		Connection con = null;
		PreparedStatement stmt = null;
		int i = 0;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, objLocal.getId());
			stmt.setString(2, objLocal.getNombre());
			stmt.setString(3, objLocal.getDireccion());
			stmt.setString(4, objLocal.getTelefono());
			stmt.setString(5, objLocal.getCorreo());
			stmt.setString(6, objLocal.getDescripcion());
			//stmt.setInt(7, objLocal.getAdministrador().getId());	
			
			i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo actualizar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return i;
	}
	
	public int eliminar(int idLocal) throws DAOExcepcion {
		String query = "EXEC KARAOKE.USP_DELETE_LOCAL ?;";
		Connection con = null;
		PreparedStatement stmt = null;
		int i = 0;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idLocal);
			
			i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo eliminar");
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return i;
	}
	
	public Collection<ILocal> buscar(ILocal objLocal) throws DAOExcepcion {
		String query = "EXEC KARAOKE.USP_GET_LOCAL ?,?,?;";
		Collection<ILocal> lista = new ArrayList<ILocal>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, objLocal.getNombre());
			stmt.setInt(2, objLocal.getAdministrador().getId());
			stmt.setInt(3, objLocal.getId());
			rs = stmt.executeQuery();
			
			ILocal vlLocal = null;
			IAdministrador vlAdministrador = null;
						
			while (rs.next()) 
			{
				vlLocal = new Local();
				vlLocal.setId(rs.getInt("idlocal"));
				vlLocal.setNombre(rs.getString("NombreLocal"));
				vlLocal.setDireccion(rs.getString("direccion"));
				vlLocal.setTelefono(rs.getString("telefono"));
				vlLocal.setCorreo(rs.getString("correo"));
				vlLocal.setDescripcion(rs.getString("descripcion"));
				
				vlAdministrador = new Administrador();
				vlAdministrador.setId(rs.getInt("idadministrador"));
				vlAdministrador.setNombre(rs.getString("Administrador"));
				vlLocal.setAdministrador(vlAdministrador);
				lista.add(vlLocal);
			}
			
		} catch (SQLException e) {			
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}		
		return lista;
	}

}
